#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
创建推荐系统相关表
"""

import mysql.connector
import logging

# 配置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': '962464',
    'database': 'news_aggregator',
    'charset': 'utf8mb4',
    'collation': 'utf8mb4_unicode_ci'
}

def create_recommendation_tables():
    """创建推荐系统相关表"""
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()
        
        # 创建用户阅读行为跟踪表
        logger.info("创建用户阅读行为跟踪表...")
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS user_reading_behavior (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                news_id INT NOT NULL,
                action VARCHAR(50) NOT NULL COMMENT '行为类型: view, click, read, share, search',
                read_duration INT DEFAULT NULL COMMENT '阅读时长（秒）',
                scroll_depth FLOAT DEFAULT NULL COMMENT '滚动深度百分比',
                device_type VARCHAR(20) DEFAULT NULL COMMENT '设备类型: desktop, mobile, tablet',
                referrer VARCHAR(255) DEFAULT NULL COMMENT '来源页面',
                extra_data JSON DEFAULT NULL COMMENT '额外数据',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                
                INDEX idx_user_id (user_id),
                INDEX idx_news_id (news_id),
                INDEX idx_action (action),
                INDEX idx_created_at (created_at),
                INDEX idx_user_action (user_id, action)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
            COMMENT='用户阅读行为跟踪表'
        """)
        logger.info("✅ 用户阅读行为跟踪表创建成功")
        
        # 创建用户兴趣标签表
        logger.info("创建用户兴趣标签表...")
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS user_interest_tags (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                tag VARCHAR(100) NOT NULL COMMENT '兴趣标签',
                weight FLOAT DEFAULT 1.0 COMMENT '权重',
                source VARCHAR(50) NOT NULL COMMENT '来源: behavior, manual, system',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                
                UNIQUE KEY uk_user_tag (user_id, tag),
                INDEX idx_user_id (user_id),
                INDEX idx_tag (tag),
                INDEX idx_weight (weight)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
            COMMENT='用户兴趣标签表'
        """)
        logger.info("✅ 用户兴趣标签表创建成功")
        
        # 创建新闻标签表
        logger.info("创建新闻标签表...")
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS news_tags (
                id INT AUTO_INCREMENT PRIMARY KEY,
                news_id INT NOT NULL,
                tag VARCHAR(100) NOT NULL COMMENT '新闻标签',
                weight FLOAT DEFAULT 1.0 COMMENT '权重',
                source VARCHAR(50) NOT NULL COMMENT '来源: auto, manual',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                
                UNIQUE KEY uk_news_tag (news_id, tag),
                INDEX idx_news_id (news_id),
                INDEX idx_tag (tag),
                INDEX idx_weight (weight)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
            COMMENT='新闻标签表'
        """)
        logger.info("✅ 新闻标签表创建成功")
        
        # 创建用户相似度缓存表
        logger.info("创建用户相似度缓存表...")
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS user_similarity_cache (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user1_id INT NOT NULL,
                user2_id INT NOT NULL,
                similarity_score FLOAT NOT NULL,
                calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                
                UNIQUE KEY uk_user_pair (user1_id, user2_id),
                INDEX idx_user1 (user1_id),
                INDEX idx_user2 (user2_id),
                INDEX idx_score (similarity_score),
                INDEX idx_calculated (calculated_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
            COMMENT='用户相似度缓存表'
        """)
        logger.info("✅ 用户相似度缓存表创建成功")
        
        # 创建推荐结果缓存表
        logger.info("创建推荐结果缓存表...")
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS recommendation_cache (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                news_id INT NOT NULL,
                recommendation_score FLOAT NOT NULL,
                algorithm_type VARCHAR(50) NOT NULL COMMENT '推荐算法类型',
                cached_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                expires_at TIMESTAMP NOT NULL,
                
                INDEX idx_user_id (user_id),
                INDEX idx_news_id (news_id),
                INDEX idx_score (recommendation_score),
                INDEX idx_algorithm (algorithm_type),
                INDEX idx_expires (expires_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
            COMMENT='推荐结果缓存表'
        """)
        logger.info("✅ 推荐结果缓存表创建成功")
        
        conn.commit()
        cursor.close()
        conn.close()
        
        logger.info("🎉 所有推荐系统表创建完成！")
        return True
        
    except Exception as e:
        logger.error(f"❌ 创建表失败: {e}")
        return False

if __name__ == '__main__':
    create_recommendation_tables()